package com.eling.elcms.travel.dao.hibernate;

import com.eling.elcms.basedoc.model.RoomType;
import com.eling.elcms.core.dao.hibernate.GenericDaoHibernate;
import com.eling.elcms.core.util.PropertyUtils;
import com.eling.elcms.system.model.CommonUser;
import com.eling.elcms.travel.dao.ICheckInDao;
import com.eling.elcms.travel.model.CheckIn;
import com.eling.elcms.travel.model.GroupReserve;
import com.eling.elcms.travel.model.view.CheckInView;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class CheckInDaoHibernate extends GenericDaoHibernate<CheckIn, Long> implements ICheckInDao {
	public CheckInDaoHibernate() {
		super(CheckIn.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<CheckInView> queryAll(CheckIn car) {
		Date endDate = PropertyUtils.getProperty(car, "checkInImplement.groupReserve.reserveDateEnd");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String start = "";
		String end = "";
		if (car.getCheckInImplement().getGroupReserve().getReserveDate() != null) {
			start = sdf.format(car.getCheckInImplement().getGroupReserve().getReserveDate());
		}
		if (endDate != null) {
			end = sdf.format(endDate);
		}
		StringBuffer sql = new StringBuffer();
		sql.append("  SELECT * ");
		sql.append("  FROM (	");
		sql.append("  	SELECT	");
		sql.append("  		t.*,t1.protocolPrice	");
		sql.append("  	FROM (	");
		sql.append("  		SELECT	");
		sql.append("  			b.`name` AS bname,ro.`code`,rt.`name` AS rname,pi.reserveDate,pi.checkInType,	");
		sql.append(
				"  			sum(CASE WHEN rp.checkIn = TRUE THEN 1 ELSE	0 END) as sum,count(rp.pkSignUpRegister) as count, ");
		sql.append(
				"  			cii.pkCIImplement,ck.registerDate,r.pkRoom,ck.operator,u.`name` AS uname,rt.pkRoomType,	");
		sql.append(
				"  			ro.pkOrgBuilding,cii.pkServicePoint,pitem.startDate,pitem.endDate,dest.`name` as destname,ck.`status`	");
		sql.append("  		FROM	");
		sql.append("  			travel_checkinimplement cii	");
		sql.append(" 			LEFT JOIN travel_groupreserve pi ON pi.pkGroupReserve = cii.pkGroupReserve	");
		sql.append(
				" 			LEFT JOIN travel_planitemgroup pgroup on pi.pkPlanItemGroup = pgroup.pkPlanItemGroup	");
		sql.append(" 			LEFT JOIN travel_planitem pitem on pgroup.pkPlanItem = pitem.pkPlanItem	");
		sql.append(" 			LEFT JOIN travel_destination dest on pitem.pkDestination = dest.pkDestination	");
		sql.append("  			LEFT JOIN travel_roompersonalInfo rp ON cii.pkCIImplement = rp.pkCIImplement	");
		sql.append("  			LEFT JOIN travel_group_roomregister room ON pi.pkGroupReserve = room.pkGroupReserve	");
		sql.append("  			LEFT JOIN travel_roomregister r ON room.pkRoomRegister = r.pkRoomRegister	");
		sql.append("  			LEFT JOIN bd_room ro ON r.pkRoom = ro.pkRoom	");
		sql.append("  			LEFT JOIN bd_building b ON ro.pkOrgBuilding = b.pkOrgBuilding	");
		sql.append("  			LEFT JOIN travel_checkin ck ON r.pkRoomRegister=ck.pkRoomRegister	");
		sql.append("  			LEFT JOIN sm_user u ON ck.operator=u.pkUser	");
		sql.append("  			LEFT JOIN travel_roomtype rt ON ro.pkRoomType = rt.pkRoomType	");
		sql.append("  		WHERE	1=1	AND cii.`status`='Confirmed' ");
		if (car.getCheckInImplement().getServicePoint() != null) {
			sql.append("  		AND	cii.pkServicePoint="
					+ car.getCheckInImplement().getServicePoint().getPkServicePoint() + "		");
		}
		if (endDate != null && car.getCheckInImplement().getGroupReserve().getReserveDate() != null) {
			sql.append("  		AND pi.reserveDate BETWEEN '" + start + "'  AND '" + end + "'  ");
		}
		sql.append("  		 	AND pi.checkInType='" + GroupReserve.CheckInType.Organization + "'	");
		sql.append("  		 GROUP BY cii.pkCIImplement,r.pkRoom ) t	");
		sql.append("  	LEFT JOIN (	");
		sql.append(
				"  		 	SELECT tr.pkOrgBuilding,tr.pkRoomType,tr.pkRoomTypePrice,tr.pkServicePoint,tr.protocolPrice,tr.saleDate FROM travel_roomtypeprice tr 	");
		sql.append("  		 	WHERE 1=1	");
		if (car.getCheckInImplement().getServicePoint() != null) {
			sql.append("  		AND	tr.pkServicePoint = "
					+ car.getCheckInImplement().getServicePoint().getPkServicePoint() + "	");
		}
		sql.append("  	) t1 ON t1.pkOrgBuilding=t.pkOrgBuilding 	");
		sql.append(
				" 		 AND t1.pkServicePoint=t.pkServicePoint AND t1.pkRoomType=t.pkRoomType AND TO_DAYS(t1.saleDate) = TO_DAYS(t.reserveDate)");
		sql.append("  ) main order by main.destname,main.startdate");
		List<Object[]> objList = getSession().createNativeQuery(sql.toString()).list();
		List<CheckInView> returnlist = new ArrayList<CheckInView>();
		for (Object[] o : objList) {
			CheckInView cv = new CheckInView();
			cv.setBname((o[0] == null ? null : o[0].toString()));
			cv.setRoomCode((o[1] == null ? null : o[1].toString()));
			RoomType rt = new RoomType();
			rt.setName((o[2] == null ? null : o[2].toString()));
			cv.setHouseType(rt);
			cv.setDestination(null == o[17] ? null : o[17].toString());
			cv.setStartDate(null == o[15] ? null : (Date) o[15]);
			cv.setEndDate(null == o[16] ? null : (Date) o[16]);
			cv.setPrice((o[19] == null ? null : Double.valueOf(o[19].toString())));
			cv.setPk(Long.valueOf(o[7].toString()));
			if (o[4].toString().equals(GroupReserve.CheckInType.Alliance.name())) {
				cv.setCheckInType(GroupReserve.CheckInType.Alliance);
			} else {
				cv.setCheckInType(GroupReserve.CheckInType.Organization);
			}
			cv.setPkRoom((o[9] == null ? null : Long.valueOf(o[9].toString())));
			if (o[10] != null && o[11] != null) {
				CommonUser cu = new CommonUser();
				cu.setPkUser(Long.valueOf(o[10].toString()));
				cu.setName(o[11].toString());
				cv.setUser(cu);
			}
			if (o[18] != null) {
				if (o[18].toString().equals(CheckIn.Status.CheckIn.name())) {
					cv.setStatus(CheckIn.Status.CheckIn);
					if (o[8] != null) {
						cv.setDate((Date) o[8]);
					}
				} else {
					cv.setStatus(CheckIn.Status.NoCheckIn);
				}
				cv.setReserveDate((Date) o[3]);
			} else {
				cv.setStatus(CheckIn.Status.NoCheckIn);
				cv.setReserveDate((Date) o[3]);
			}
			returnlist.add(cv);
		}
		if (car.getStatus() != null) {
			List<CheckInView> list = new ArrayList<CheckInView>();
			for (CheckInView civ : returnlist) {
				if (civ.getStatus().name().equals(car.getStatus().name())) {
					list.add(civ);
				}
			}
			return list;
		} else {
			return returnlist;
		}
	}

}
